﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using ICSharpCode.SharpZipLib.Zip;

namespace Tool
{
    public partial class Form1 : Form
    {
        #region properties
        OpenFileDialog ofd = new OpenFileDialog();
        //BackgroundWorker bgw = new BackgroundWorker();
        string strFileTxt;
        string strDateBegin;
        string strDateEnd;
        string strNAME;

        bool checkError = false;        
        string str;
        int intMax;
        #endregion

        public Form1()
        {
            InitializeComponent();
            //progress bar
            progressBar1.Minimum = 0;
            progressBar1.Value = 0;
            progressBar1.Step = 1;
        }

        private void btnLoadFileExcel_Click(object sender, EventArgs e)
        {
            try
            {
                lbComplete.Text = "";
                progressBar1.Value = 0;
                ofd.Filter = "File Excel (*.xls.*.xlsx)|*.xls;*.xlsx";
                if (ofd.ShowDialog() == DialogResult.OK)
                    tbPath.Text = ofd.FileName;
                strNAME = ofd.FileName.Substring(ofd.FileName.IndexOf(".") + 1, 8);
            }
            catch { }
        }               

        private void btnConvertToText_Click(object sender, EventArgs e)
        {           
            btnLoadFileExcel.Enabled = false;
            btnConvertToText.Enabled = false;
            btnClose.Enabled = false;
            bgw.RunWorkerAsync(); 
        }
        
        private void textToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                System.Diagnostics.Process.Start(strFileTxt);
            }
            catch { }
        }

        private void excelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                System.Diagnostics.Process.Start(ofd.FileName);
            }
            catch { }
        }        
        private void openFileExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string strFolder = strFileTxt.Substring(0, strFileTxt.LastIndexOf(@"\"));
                System.Diagnostics.Process.Start(strFolder);
            }
            catch { }
        }
        private void bgw_DoWork_1(object sender, DoWorkEventArgs e)
        {
            try
            {
                convertExcelToText();
            }
            catch 
            {
                MessageBox.Show("Please choose the path..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }           
        }

        private void bgw_ProgressChanged_1(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Maximum = intMax;
            progressBar1.Value = e.ProgressPercentage;
            int index = (progressBar1.Value * 100) / progressBar1.Maximum;
            lbComplete.Text = "Convert " + index + " %....";            
        }

        private void bgw_RunWorkerCompleted_1(object sender, RunWorkerCompletedEventArgs e)
        {
            if ((progressBar1.Value > 0) && (checkError == false))
            {
                lbComplete.Text = "Convert successful...";
                MessageBox.Show("Convert complete !", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            if ((progressBar1.Value > 0) && (checkError == true)) 
            {
                lbComplete.Text = "Convert error..";
                MessageBox.Show("Lỗi dữ liệu không phải số nguyên hoặc nhỏ hơn 0..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            btnLoadFileExcel.Enabled = true;
            btnConvertToText.Enabled = true;
            btnClose.Enabled = true;
            
        }
        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        #region convert file Excel to file text
        public void convertExcelToText()
        {
            #region writ file text
            strDateBegin = getDate(dateBegin);
            strDateEnd = getDate(dateEnd);
            strNAME = "#BG#" + strDateBegin + "#" + strNAME + "#";

            //str = ofd.FileName.Substring(ofd.FileName.LastIndexOf(@"\"));                  
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, @"\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            //strFileTxt = ofd.FileName.Substring(0, ofd.FileName.LastIndexOf(@".")) + ".txt";
            strFileTxt = ofd.FileName.Substring(0, ofd.FileName.LastIndexOf(@"."));
            if (File.Exists(strFileTxt) == true) 
            {
                if (MessageBox.Show("File exists, do you want to replace a new file ?", "Information", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
                {
                    File.Delete(strFileTxt);
                }
                else
                    return;
            }
               
            StreamWriter sw = new StreamWriter(strFileTxt,true,Encoding.UTF8);

            UTF8Encoding utf8 = new UTF8Encoding();
            Encoding windows1258 = Encoding.GetEncoding(1252);
            byte[] encodedBytes;

            intMax = range.Columns.Count * (range.Rows.Count - 1);        

            string[] strC = new string[range.Columns.Count];
            for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                if (cCnt > 47)
                    break;
                try
                {
                    strC[cCnt - 1] = (range.Cells[1, cCnt] as Excel.Range).Value2.ToString();
                }
                catch
                {
                    strC[cCnt - 1] = "";
                }
            }          
            //begin write
            sw.WriteLine(strNAME);
            for (int rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
            {                
                int i = rCnt - 2;
                for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    if (cCnt > 47)
                    {
                        sw.WriteLine();
                        break;
                    }
                    bgw.ReportProgress(i * range.Columns.Count + cCnt);                    
                    try
                    {
                        str = (range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
                        str = str.Replace('\n', ' ');                        
                        
                        //replace 0 equal Null
                        if (str.Length == 1)
                            if (str.Equals("0"))
                                str = "NULL";

                        //check do dai du lieu
                        if (str.Length > 50) 
                        {
                            sw.WriteLine("#" + strC[cCnt - 1] + "Loi du lieu do dai du lieu lon hon 50");
                            break;
                        }

                        //check du lieu la so thi phai lon hon 0 va la so nguyen
                        try
                        {                           
                            //kiem tra so nguyen
                            if (str.IndexOf('.') > 0)
                            {
                                string[] strN = str.Split('.');
                                try
                                {
                                    int intCh = Convert.ToInt32(strN[0]);
                                    intCh = Convert.ToInt32(strN[1]);
                                    sw.WriteLine("#loi du lieu khong phai so nguyen");
                                    checkError = true;
                                    sw.Close();
                                    return;
                                }
                                catch 
                                {
                                
                                }
                                
                            }

                            //check so nguyen >0
                            int number = Convert.ToInt32(str);
                            if (number < 0)
                            {
                                sw.WriteLine("#loi du lieu nho hon 0");
                                checkError = true;
                                sw.Close();
                                return;
                            }
                        }
                        catch { }

                        if (cCnt == 1)
                            str = "#" + strC[cCnt - 1] + str;
                        else
                            str = strC[cCnt - 1] + str;
                    }
                    catch
                    {
                        //check column 1 empty
                        if (cCnt == 1)
                            break;
                        //set value for column empty
                        sw.Write(strC[cCnt - 1] + "NULL#");

                        if (cCnt % range.Columns.Count == 0)
                        {
                            sw.WriteLine();
                        }
                        continue;
                    }
                    
                    sw.Write(str + "#");
                    if (cCnt % range.Columns.Count == 0)
                    {
                        sw.WriteLine();
                    }
                }
            }
            strNAME = "#EN#" + strDateEnd + "#" + (range.Rows.Count - 1) + "#"; ;
            sw.WriteLine(strNAME);
            sw.Close();           
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            #endregion writ file text

            #region nen file text va dat pass

            try
            {
                string zipFile = strFileTxt + ".zip";
                if (System.IO.File.Exists(zipFile)) 	//  Small piece of code
                // to delete zip file if it already exists
                {
                    System.IO.File.Delete(zipFile);
                }

                ZipOutputStream zipOut = new ZipOutputStream(File.Create(zipFile));
                if (!string.IsNullOrEmpty(tbPass.Text.Trim()))
                {
                    zipOut.Password = tbPass.Text.Trim();
                }
                zipOut.SetLevel(9);
                
                FileInfo fileInfo = new FileInfo(strFileTxt);
                ZipEntry entry = new ZipEntry(fileInfo.Name);
                FileStream fileStream = File.OpenRead(strFileTxt);
                byte[] buffer = new byte[Convert.ToInt32(fileStream.Length)];
                fileStream.Read(buffer, 0, (int)fileStream.Length);
                entry.DateTime = fileInfo.LastWriteTime;
                entry.Size = fileStream.Length;
                fileStream.Close();

                zipOut.PutNextEntry(entry);
                zipOut.Write(buffer, 0, buffer.Length);
      
                zipOut.Finish();
                zipOut.Close();

                if (System.IO.File.Exists(strFileTxt)) 	//  Small piece of code
                // to delete zip file if it already exists
                {
                    System.IO.File.Delete(strFileTxt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            #endregion nen file text va dat pass
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        public byte[] getBytes(string str) 
        {
            byte[] byteArray = new byte[str.Length];
            char[] charArray = str.ToCharArray();
            for (int i = 0; i < byteArray.Length; i++) 
            {
                byteArray[i] = Convert.ToByte(charArray[i]);
            }
            return byteArray;
        }

        public string getDate(DateTimePicker dateTime) 
        {            
            int day;
            int month;
            int year;
            string strDate = null;
            string[] date = dateTime.Value.ToShortDateString().Split('/');

            day = Convert.ToInt16(date[0]);
            month = Convert.ToInt16(date[1]);
            year = Convert.ToInt16(date[2]);

            if (day < 10)
                strDate += "0" + day;
            else
                strDate += day;
            if (month < 10)
                strDate += "0" + month;
            else
                strDate += month;

            year = year % 100;
            if (year < 10)
                strDate += "0" + year;
            else
                strDate += year;
            return strDate;
        }
        #endregion   
    }
}
